import re
import redis


def get_t_or_v_fields(table_name, path, win_conn, db_conn):
    '''
    获取视图或表字段
    :param table_name: 表或视图名(例如：dbo_表名， View_视图名)
    :param path: 远程主机cube路径
    :return:
    '''
    redis_conn = redis.Redis(host='127.0.0.1', port=6379, db=12)
    redis_key = "{}-{}-dsv-fields".format(win_conn.host, path)
    if not redis_conn.get(redis_key):
        file_names = win_conn.get_files('*.dsv.xml', path)
        content = ''
        for file_name in file_names:
            if 'dsv' in file_name:
                content = win_conn.get_file_connect(file_name, path)
        redis_conn.set(redis_key, content, ex=60 * 60 * 24)  # ex参数设置60s过期
    else:
        content = redis_conn.get(redis_key).decode('utf-8')
    redis_conn.close()
    # 按<>匹配，找到量值名称所在<>
    pat = re.compile('<xs:element name="{}(.*?)msprop:QueryBuilder="SpecificQueryBuilder">'.format(table_name))
    content_list = pat.findall(content.replace('\r\n', ''))
    # 新策略
    sql_pat = re.compile('msprop:QueryDefinition="(.*)"')
    sql = ""

    if 'dbo_' not in table_name:
        for item in content_list:
            # 替换掉所有特殊符号
            sql = sql_pat.findall(
                item.replace('&#xD;&#xA;', ' ').replace('\r\n', ' ').replace("&lt;", '<').replace('&gt;', '>'))[0]
        fields = db_conn.get_field_name(sql)
        return fields
    else:
        replace_table_name = table_name.replace('dbo_', 'dbo.')
        sql = 'select * from {}'.format(replace_table_name)
        fields = db_conn.get_field_name(sql)
        return fields

    # # 从content_list中匹配出字段
    # field_pat = re.compile('SELECT(.*?)FROM')
    # fields = []
    # for item in content_list:
    #     fields = field_pat.findall(item.replace(' ', '').replace('&#xD;&#xA;', '').replace('\r\n', ''))
    # # 处理字段
    # if len(fields) != 0:
    #     fields = fields[0].split(',')
    #     for index, field in enumerate(fields):
    #         if "AS" in field:
    #             fields[index] = field.split('AS')[-1]
    # else:
    #     replace_table_name = table_name.replace('dbo_', 'dbo.')
    #     sql = 'select * from {}'.format(replace_table_name)
    #     fields = db_conn.get_field_name(sql)
    # return fields


def get_dimension_fields(table_name, db_conn):
    '''
    获取维度表字段
    :param table_name: 维度表名
    :param db_conn: 维度表所在数据库连接
    :return:
    '''
    replace_table_name = table_name.replace('dbo_', 'dbo.')
    sql = 'select * from {}'.format(replace_table_name)
    fields = db_conn.get_field_name(sql)
    return {table_name: fields}


if __name__ == '__main__':
    from app_cube.utils.connect.my_win import MyWin
    from app_cube.utils.connect.my_db import MyDB

    db_conn = MyDB('sqlserver', '127.0.0.1', 1433, 'sa', '123456', 'YWC_DW')

    path = r'C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data\YWC_SSAS.0.db'
    win_conn = MyWin('127.0.0.1', 'Administrator', 'zjs')
    result = get_t_or_v_fields(table_name='View_Fact_Inhosp_Fee_Detail', win_conn=win_conn, path=path, db_conn=db_conn)
    print(result)
    result = get_dimension_fields('dbo_Dim_Drug', db_conn)
    print(result)
